---
title: "SQL-034: Policy association list"
summary: Retrieve associated policies across manual, relationship, discount, and experience combinations.
status: draft
owner: SAIF Corporation
last_reviewed: 2026-08-21
library: SAIF.Business.View.Policy
reference_type: embedded
---

# SQL-034 Policy association list

This SQL retrieves the full policy association list for a policy by unioning manual relationships, policy relationships, discount combinations, and experience combinations.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-034 |
| **Operation type** | Read |
| **Execution method** | Inline |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | `PolicyAssociationList` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `GetPolicyAssociationList` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:PolicyNumber` | Input | Decimal | N/A | Policy number whose association list is requested. |
| `:cppDate` | Input | Date | N/A | Combined-policy-period date used to filter discount combinations. |
| `:ecDate` | Input | Date | N/A | Experience-combination date used to filter experience combinations. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [POLICY_RELATIONSHIP](../database-tables.md#policy_relationship) | SELECT | `mbr_policy_num`, `affil_typ_cd`, `parent_policy_num` |
| [POLICY](../database-tables.md#policy) | SELECT | `policy_num`, `policy_relationship_code`, `policy_relationship_policy_num`, `rate_account` |
| [COMBO_POLICY_PERIOD](../database-tables.md#combo_policy_period) | SELECT | `policy_num`, `active_member_flag`, `policy_period_eff_date`, `combo_period_seq_num`, `min_premium_member_flag` |
| [POLICY_PERIOD](../database-tables.md#policy_period) | SELECT | `policy_num`, `policy_period_eff_date`, `policy_period_exp_date` |
| [ER_COMBINATION_POLICY](../database-tables.md#er_combination_policy) | SELECT | `policy_num`, `eff_date`, `exp_date`, `er_combination_seq_num` |

## Sample SQL

```sql
-- Reconstructed from ManualAffiliationSQL UNION PolicyRelationshipSQL
--                    UNION DiscountCombinationSQL UNION ERCombinationSQL
SELECT
  mbr_policy_num AS PolicyNumber,
  'M.' || pr.affil_typ_cd AS RelationshipType,
  DECODE(parent_policy_num, mbr_policy_num, 'Y', 'N') AS IsParent
FROM policy_relationship pr
WHERE (pr.parent_policy_num = :PolicyNumber
   OR pr.parent_policy_num = (
     SELECT
       parent_policy_num
     FROM policy_relationship
     WHERE mbr_policy_num = :PolicyNumber))
UNION
SELECT DISTINCT
  parent_policy_num AS PolicyNumber,
  'M.' || pr.affil_typ_cd AS RelationshipType,
  CASE
    WHEN parent_policy_num = (
      SELECT DISTINCT
        parent_policy_num
      FROM policy_relationship
      WHERE mbr_policy_num = :PolicyNumber
         OR parent_policy_num = :PolicyNumber) THEN 'Y'
    ELSE 'N'
  END AS IsParent
FROM policy_relationship pr
WHERE (pr.parent_policy_num = :PolicyNumber
   OR pr.parent_policy_num = (
     SELECT DISTINCT
       parent_policy_num
     FROM policy_relationship
     WHERE mbr_policy_num = :PolicyNumber))
UNION
SELECT
  policy_num AS PolicyNumber,
  p.policy_relationship_code,
  DECODE(policy_relationship_policy_num, policy_num, 'Y', 'N')
FROM POLICY p
WHERE (p.policy_relationship_policy_num = :PolicyNumber
   OR p.policy_relationship_policy_num = (
     SELECT
       policy_relationship_policy_num
     FROM POLICY
     WHERE policy_num = :PolicyNumber))
UNION
SELECT
  cpp2.policy_num AS PolicyNumber,
  'DiscountCombination',
  NVL(cpp2.min_premium_member_flag, 'N')
FROM POLICY p,
  combo_policy_period cpp,
  policy_period pp,
  combo_policy_period cpp2
WHERE cpp.policy_num = :PolicyNumber
  AND cpp.active_member_flag = 'Y'
  AND cpp.policy_period_eff_date = pp.policy_period_eff_date
  AND cpp.policy_num = pp.policy_num
  AND pp.policy_period_eff_date <= :cppDate
  AND pp.policy_period_exp_date > :cppDate
  AND cpp.combo_period_seq_num = cpp2.combo_period_seq_num
  AND cpp2.active_member_flag = 'Y'
  AND cpp2.policy_num = p.policy_num
  AND cpp.active_member_flag = 'Y'
UNION
SELECT
  ecp2.POLICY_NUM AS PolicyNumber,
  'ExperienceCombination',
  DECODE(ecp2.policy_num, p.rate_account, 'Y', 'N')
FROM policy p,
  er_combination_policy ecp,
  er_combination_policy ecp2
WHERE ecp.policy_num = :PolicyNumber
  AND ecp.eff_date <= :ecDate
  AND NVL(ecp.exp_date, :ecDate + 1) > :ecDate
  AND ecp.policy_num = p.policy_num
  AND ecp.er_combination_seq_num = ecp2.er_combination_seq_num
  AND ecp2.eff_date <= :ecDate
  AND NVL(ecp2.exp_date, :ecDate + 1) > :ecDate
```

## Operations and processes
- [Retrieve policy associations](../operations-processes.md)

## Business rules
- [BR-VP-014: Source policy excluded from associations by default](../business-rules.md#br-vp-014-source-policy-excluded-from-associations-by-default)
- [BR-VP-024: Default association dates](../business-rules.md#br-vp-024-default-association-dates)

## Sources
- `SAIF.Business.View.Policy/PolicyAssociations.vb`